How to filter a mysql database with user input on a website and then spit the filtered table back to the website? [migrated]
Posted
by
Luke
on Pro Webmasters
See other posts from Pro Webmasters
or by Luke
Published on 2012-09-30T11:22:06Z
Indexed on
2012/09/30
15:50 UTC
Read the original article
Hit count: 426
I've been researching this on google for literally 3 weeks, racking my brain and still not quite finding anything. I can't believe this is so elusive. (I'm a complete beginner so if my terminology sounds stupid then that's why.)
I have a database in mysql/phpmyadmin on my web host. I'm trying to create a front end that will allow a user to specify criteria for querying the database in a way that they don't have to know sql, basically just combo boxes and checkboxes on a form. Then have this form 'submit' a query to the database, and show the filtered tables.
This is how the SQL looks in Microsoft Access:
PARAMETERS TEXTINPUT1 Text ( 255 ), NUMBERINPUT1 IEEEDouble;
// pops up a list of parameters for the user to input
SELECT DISTINCT Table1.Column1, Table1.Column2, Table1.Column3,*
// selects only the unique rows in these three columns
FROM Table1
// the table where this query is happening
WHERE (((Table1.Column1) Like TEXTINPUT1] AND ((Table1.Column2)<=[NUMBERINPUT1] AND ((Table1.Column3)>=[NUMBERINPUT1]));
// the criteria for the filter, it's comparing the user input parameters to the data in the rows and only selecting matches according to the equal sign, or greater than + equal sign, or less than + equal sign
What I don't get: WHAT IN THE WORLD AM I SUPPOSED TO USE (that isn't totally hard)!?
I've tried google fusion tables - doesn't filter right with numerical data or empty cells in rows, can't relate tables
I've tried DataTables.net, can't filter right with numerical data and can't use SQL without a bunch of indepth knowledge, not even sure it can if you have that..
I've looked into using jQuery with google spreadsheets, doesn't work at all either
I have no idea how I'm supposed to build a front end with my database. Every place that looks promising (like zohocreator) is asking for money, and is far too simplified to be able to do the LIKE criteria or SELECT DISTINCT stuff.
© Pro Webmasters or respective owner